import sqlite3
import time
from easysnmp import snmp_bulkwalk
import threading


class DataBase(object):
    def __init__(self):
        """
         初始化数据库链接
        """
        self.con = sqlite3.connect("DataBase.db")
        self.cur = self.con.cursor()

    def sql_executemany(self, sql, date):
        """
        批量插入数据库数据
        @param sql: 插入的sql语句
        @type sql:
        @param date: 插入的数据列表
        @type date:
        @return:
        @rtype:
        """
        with self.con:
            try:
                self.cur.executemany(sql, date)
                print("数据库操作成功！")
            except:
                self.con.rollback()
                print("数据库操作失败！")

    def sql_excute(self, sql):
        """
        单条sql语句执行器
        @param sql: 传入执行的sql语句
        @type sql:
        @return: 返回sql语句执行的结果
        @rtype:
        """
        with self.con:
            try:
                return self.cur.execute(sql)
            except:
                self.con.rollback()
                print("数据库操作失败！")

    def sql_to_dict(self, fun):
        """
        格式化输出数据库中所有设备信息，并以字典的形式返回
        @param fun:指定查询的sql语句
        @type fun:
        @return: 返回设备的信息
        @rtype:
        """
        return [{"hostname": i[1],
                 "ip": i[2],
                 "user": i[3],
                 "passwd": i[4],
                 "company": i[5],
                 "version": i[6],
                 "classification": i[7]}
                for i in fun]

    def select_all_device_info(self):
        """
        查询所有设备的信息
        @return: 以字典的形式返回所有设备的信息
        @rtype:
        """
        sql = "SELECT * FROM t_device;"
        return self.sql_to_dict(self.sql_excute(sql))

    def select_zww_device_info(self):
        """
        只查询楼层的设备
        @return: 以字典的形式返回所有设备的信息
        @rtype:
        """
        sql = "SELECT * FROM t_device WHERE classification = 'ZWW';"
        return self.sql_to_dict(self.sql_excute(sql))

    def create_interface_table(self):
        """
        创建接口的状态表
        @return:
        @rtype:
        """
        table = """
CREATE TABLE "t_interface_status" (
	"s_id"	INTEGER NOT NULL UNIQUE,
	"ip"	TEXT NOT NULL,
	"port"	TEXT NOT NULL,
	"status"	INTEGER NOT NULL DEFAULT 0,
	"date"	TimeStamp NOT NULL DEFAULT (date('now', 'localtime')),
	"time"	TimeStamp NOT NULL DEFAULT (time('now', 'localtime')),
	PRIMARY KEY("s_id" AUTOINCREMENT)
)
        """
        self.sql_excute(table)
        print("接口表创建成功！")

    def insert_interface_status(self, date):
        """
        往数据库中插入获取到的接口状态
        @param date: 获取的接口状态列表
        @type date:
        @return:
        @rtype:
        """
        start = time.time()
        sql = "INSERT INTO t_interface_status (ip,port,status) VALUES (?,?,?);"
        self.sql_executemany(sql, date)
        print("接口状态插入数据库成功！用时：", time.time() - start)


def get_interface_info(ip):
    """
    通过snmp协议获取接口状态信息
    @param ip:
    @type ip:
    @return:
    @rtype:
    """
    interface_list = [i.value for i in
                      snmp_bulkwalk('IF-MIB::ifDescr', hostname=ip, community="admin1234", version=2)]
    status_list = [i.value for i in
                   snmp_bulkwalk('IF-MIB::ifOperStatus', hostname=ip, community="admin1234", version=2)]
    return [(ip, x, y) for x, y in zip(interface_list, status_list)]


def get_snmp():
    device_list = DataBase().select_zww_device_info()
    for i in device_list:
        try:
            print(f"正在获取 {i['hostname']} 的接口信息！")
            DataBase().insert_interface_status(get_interface_info(i["ip"]))
        except:
            print(f"正在获取 {i['hostname']} 的接口信息失败！")
            continue


def run(ip):
    try:
        print(f"正在获取 {ip} 的接口信息！")
        DataBase().insert_interface_status(get_interface_info(ip))
    except:
        print(f"正在获取 {ip} 的接口信息失败！")


def main():
    start = time.time()
    database = DataBase()
    database.create_interface_table()
    # get_snmp()
    device_list = database.select_zww_device_info()
    for i in device_list:
        t = threading.Thread(target=run, args=(i["ip"],))
        t.start()
    t.join()
    print(time.time() - start)


if __name__ == '__main__':
    main()
